package com.bw.dao;

import com.bw.bean.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Service;

import java.util.List;

@Mapper
public interface EmpDao {

    @Select("select HOUR(create_time) `name`,count(*) `value` from user_chapter_process group by HOUR(create_time)")
    List<Emp> getList1();

    @Select("select\n" +
            "tp.paper_title `name`,\n" +
            "count(te.user_id) num,\n" +
            "sum(te.score)/count(te.user_id) `value`,\n" +
            "sum(te.duration_sec)/count(te.user_id) `num2`\n" +
            "from test_exam te \n" +
            "LEFT JOIN test_paper tp on te.paper_id=tp.id\n" +
            "group by tp.paper_title")
    List<Emp2> getList2();

    @Select("select \n" +
            "(select count(*) from payment_info where date_format(create_time,'%Y-%m-%d')='2021-04-23')\n" +
            "-\n" +
            "(select count(*) from payment_info where date_format(create_time,'%Y-%m-%d')='2021-04-22') `value`")
    Double getList3();

    @Select("select c.course_name `name`,a.avg1,a.num1,b.num2,b.num2/a.num1 `avg2` from\n" +
            "(select course_id,avg(review_stars) `avg1`,count(user_id) `num1` from review_info group by course_id) a,\n" +
            "(select course_id,count(*) `num2` from review_info where review_stars>=3 group by course_id) b,\n" +
            "course_info c\n" +
            "where a.course_id=b.course_id and a.course_id=c.id")
    List<Emp3> getList4();


    @Select("select HOUR(create_time) `name`,count(*) `value` from user_chapter_process group by HOUR(create_time)\n")
    List<Emp> getList5();

    @Select("select  tqi.question_txt `name`,a.num,b.num1,b.num1/a.num lv  from\n" +
            "(select question_id,count(*) num  from test_exam_question  group by question_id) a,\n" +
            "(select question_id,count(*) num1 from test_exam_question where is_correct=1   group by question_id) b,\n" +
            "test_question_info  tqi\n" +
            "where a.question_id=b.question_id     and a.question_id=tqi.id")
    List<Emp4> getList6();

    @Select("select \n" +
            "tp.paper_title `name`,\n" +
            "count(te.user_id) `num`,\n" +
            "avg(te.score) `avg`,\n" +
            "avg(te.duration_sec) `avg2`\n" +
            "from test_exam te \n" +
            "LEFT JOIN test_paper tp on te.paper_id=tp.id\n" +
            "group by tp.paper_title\n")
    List<Emp5> getList7();

    @Select("select \n" +
            "bs.source_site `name`,\n" +
            "count(oi.user_id) `value`\n" +
            "from order_info oi\n" +
            "LEFT JOIN order_detail od on oi.id = od.order_id\n" +
            "LEFT JOIN video_info vi on od.course_id = vi.course_id\n" +
            "LEFT JOIN base_source bs on bs.id = vi.video_source_id\n" +
            "where bs.source_site is not null\n" +
            "group by bs.source_site")
    List<Emp> getList8();
}
